The KPI designer is comprised of five main steps to configure the logic. The KPI module in formulate acts like a wizard to step the use through this building process.
- KPI formulations can be scripted in PQL or MDX.
- Actual, Target and Status values are all based on 'formula' building described in detail here.
KPI Steps
Building KPIs involves these five key steps:
- Setting the KPI's default graphic style
- Setting the logic for the KPI's actual value
- Setting the logic for the KPI's target value
- Setting the logic for the KPI's status value
- Setting the logic for the KPI's bands (to indicate how well the KPI status is compared to benchmarks)
These steps are described below.
KPI Style / Graphic
Start here by selecting the required graphical indicator style for the KPI. When the KPI is visualized in Discover, the chosen KPI style will be used in the visual as the default status indicator. These indicators can be used to visualize the KPI status in both matrix grids and gauges.
Note that end users can the default graphical representation using the conditional formatting tools.
In the example below, the Reverse Signs style was selected and then added to a gauge visual.
Dials and Bullets
The dial and bullet graphic styles behave a little differently than the other KPI styles. The rest of the styles can be used as a status indicator in both matrix grid and gauge visualizations. Dials, however, are designed specifically for use in the gauge visual; if a dial indicator is added to a grid, the text indicator will be rendered instead of the dial.
Likewise, the 'bullet' style is designed for use with the bullet chart. While it does not produce a status indicator, it updates the drop zones to remove the indicator zone, which is not required for the bullet chart.
KPI Actual Value
The actual value represents the level of ACTUAL performance. Using the tool panel, add the required blocks and functions in order to write the actual value expression. Element, Aggregation, and Date blocks (red highlight below) must be injected with the required model metadata from the properties panel (blue highlight).
- Actual values are designed using the same framework as normal formulas, described in detail here.
Context Panel
The context panel (green highlight above) displays the location where the KPI will be saved. Each KPI must have a designated parent dimension and hierarchy (in the Properties panel). Building a KPI creates custom measures, and as such, they are automatically saved in the measures dimension and hierarchy.
Format Panel
The value's format string may be changed from the Format panel (orange highlight). Users can apply a format string to each custom measure within the KPI, in order to determine how metrics for the custom measure are formatted. Format string options include a variety of presets, like currency, percentage, and standard number masks like "#,#".
Step Preview Panel
Users can test KPIs at any step in the KPI builder, by clicking the execute button (yellow arrow). The Step Preview panel (yellow highlight) will display a preview of the results for that step.
KPI Target Value
The intended GOAL or TARGET of performance against which the actual outcome is to be measured. The capabilities are identical to the Actual value UI described above.
- Target values are designed using the same framework as normal formulas, described in detail here.
KPI Status Value
"Status" is how the actual compares to the target. The capabilities are identical to the Actual value UI described above.
Use the tool-panel to write the status value formulation, or choose a formula from the Templates drop down in the ribbon. The formula that you configure is used to generate the status value.
- Status values are designed using the same framework as normal formulas, described in detail here.
Template Formulas
There are five simple formulas available from the Templates drop down to accelerate the classic ways of calculating status.
- Ratio: Actual/ Target
- Reverse Ratio: Target/ Actual
- Growth Percentage: Actual - Target / Target
- Variance Percentage: Actual - Target / Actual
- Difference: Actual - Target
KPI Bands
Bands are used to give the end user a sense of the status value of the outcome. Usually, a status value may show as red, if its value is poor, or green if it is doing well. Designers can generate 2,3,4 or 5 bands using static values or advanced formulas so that the bands dynamically resolve intelligently.
To begin, define the ranges (bands) for the KPI. Start by enabling bands:
Next, choose the KPI model; the model selected determines the number of bands. The following options are available:
Next, insert static or dynamic values into each band; the band ranges affect the KPI color display. You may select a static value, a formula, parameter, or evaluation set. Using the more advanced band options allows for dynamic banding for KPI values - which produces a more efficient and more intelligent solution.
- Click here for more information about KPI dynamic banding.
Band Colors
Band colors are NOT defined in the KPI designer tools. Instead they are derived by default from the prevailing theme used in the report or dashboard. Alternatively, users can edit the colors of the bands using the Conditional Formatting dialog box.